At 9:10 pm -0500 23/8/99, Kevin Heflin wrote:
>I've got a single table which lists usernames, addressinfo and signup
>dates
>
>signup date is data type 'date'
>
>I'm wondering if it's possible through the magic of SQL to run a query
>like:
>
>select * from table where signupdate > '01/01/1999' and < '04/01/1999';
Hi Kevin,
Two ways to define a range:
WHERE signupdate > '01/01/1999'
AND signupdate < '04/01/1999';
or using syntaxtic sugar:
WHERE signupdate BETWEEN '01/01/1999' AND '04/01/1999';
>then in the results, I would like to break this down by months. So I can
>show total signups for january 1999, then February 1999 etc..
Try something like the following:
SELECT date_part('month',date_report) AS month,
date_part('year',date_report) AS year, count(*) AS sign_ups
FROM path_reports
WHERE date_report BETWEEN '01/01/1950' and '01/01/2000'
GROUP BY by 1,2 -- group by year and month
ORDER BY 3; -- order by if you want a different order then implied by group
in this instance we sort by number of signups
HTH,
S.
+--------------------------+--------------------------------------+
| Stuart C. G. Rison | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street |
| N.B. new phone code!! | London, W1P 8BT |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM |
| Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk |
+--------------------------+--------------------------------------+
